#pragma once

// MotleyMono data layer  SQL statement pattern,
// use FORMAT to get the final statement 
//	SQL statement pattern:  
//		#deifne   SQL_SEARCH_JUGYOIN	_T("select * from T_Jugyoin where id=%d")
//	or
//		static LPCTSTR Lpsz_QueryMeisaiAriJugyoin = 
//		"SELECT	jugyoinid "
//		"FROM	T_ShoyoMeisai "
//		"WHERE	shoyoshikyuid=%d";

//	Code:
//		IString strSQL;
//		strSQL.Format( SQL_SEARCH_JUGYOIN, jugyoinId);
//		if(query.SetSQLString(strSQL) != true) {
//			.
//			.
//		}

//EXAMPLE:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
	//#define SQL_ByShikybiGetZenkaiMeishaiNyuryoku_ForShoyo	\
	//							_T("SELECT top 1	T_MeisaiKomokuNyuryoku.Id,	T_MeisaiKomokuNyuryoku.ShikyuKubun,							\								\
	//								FROM			T_MeisaiKomokuNyuryoku, T_KyuyoMeisai,  T_KyuyoShikyu		\
	//								where			T_KyuyoMeisai.Id= T_MeisaiKomokuNyuryoku.KyuyoMeisaiId and		\
	//												T_MeisaiKomokuNyuryoku.MeisaiKomokuId=%ld and T_MeisaiKomokuNyuryoku.ShikyuKubun = %ld and	\
	//												T_KyuyoMeisai.JugyoinId= %ld and  T_KyuyoMeisai.KyuyoShikyuId=T_KyuyoShikyu.Id	and		\
	//												T_KyuyoShikyu.Shikyubi <= %lu \
	//								order by		T_KyuyoShikyu.Shikyubi DESC, T_KyuyoShikyu.KyuyoGetsudoId DESC")
	//#define SQL_GetKyuyoHendoKomokuNyuryokuListByJugyoinGetsudo1																	\
	//							_T("select T_KyuyoMeisai.Id, T_KyuyoMeisai.HakkoTag from T_KyuyoMeisai "							\
	//									"inner join T_KyuyoShikyu on T_KyuyoShikyu.Id = T_KyuyoMeisai.KyuyoShikyuId "				\
	//									"where (T_KyuyoShikyu.MeisaiShubetsu = %d) AND "											\
	//										"(T_KyuyoShikyu.KyuyoGetsudoId = %d) AND (T_KyuyoMeisai.JugyoinId = %d)")
	//#define SQL_GetJugyoinIdAndMeisaiCount																					\
	//					"("																									\
	//					"SELECT T_KyuyoMeisai.JugyoinId AS X2, COUNT(*) AS Y2 FROM T_KyuyoMeisai"							\
	//					" INNER JOIN T_KyuyoShikyu ON T_KyuyoMeisai.KyuyoShikyuId = T_KyuyoShikyu.Id"						\
	//					" WHERE (T_KyuyoShikyu.KyuyoGetsudoId = %d and T_KyuyoShikyu.MeisaiShubetsu <> %d)"					\
	//					" GROUP BY T_KyuyoMeisai.JugyoinId"																	\
	//					") AS T2"
	//#define SQL_GetJugyoinIdANDNewestKyuyoShikyubiWhitchHasMeisaiInner														\
	//					"("																									\
	//					"SELECT T_KyuyoMeisai.JugyoinId AS X3, MAX(T_KyuyoShikyu.Shikyubi) AS Y3 FROM T_KyuyoMeisai"		\
	//					" INNER JOIN T_KyuyoShikyu ON T_KyuyoMeisai.KyuyoShikyuId = T_KyuyoShikyu.Id"						\
	//					" WHERE (T_KyuyoShikyu.Shikyubi < %d)"																\
	//					" GROUP BY T_KyuyoMeisai.JugyoinId"																	\
	//					") AS T3"

CString SQL_CreateDB						=	_T("IF object_id('%s') IS NULL CREATE DATABASE %s;");
static LPCTSTR SQL_DropDB					=	_T("IF object_id('%s') IS NOT NULL DROP DATABASE %s;");
//#define SQL_CreateTB_Mono_PageList	_T("CREATE TABLE pagelist(					\
//											id INT(4) NOT NULL DEFAULT '0'			\
//											);");
static LPCTSTR SQL_DropTB					=	_T("DROP TABLE IF %s;");
static LPCTSTR SQL_SelectAllFromOneTable	=	_T("SELECT * FROM %s;");
//#define SQL_InsertTB	_T("INSERT INTO %s(id, name, birhtday) VALUES ('1', 'zongtongyi', '19830611');");
//#define SQL_UpdateTB	_T("UPDATE %s SET name='zongtongyi', birthday='19830611' WHERE id='1';");
//#define SQL_DeleteTB	_T("DELETE FROM %s WHERE id='1';");
//static LPCTSTR Lpsz_QueryExample =	"SELECT	jugyoinid "
//											"FROM	T_ShoyoMeisai "
//											"WHERE	shoyoshikyuid=%d";

CString SQL_CreateTab1					= _T("IF object_id('T_Tab1') IS NULL CREATE TABLE T_Tab1(id_SinglePageData INT NOT NULL, \
												name			VARCHAR(10),	\
												nickname		VARCHAR(30),	\
												birthday		datetime,		\
												age				INT,			\
												twelve			INT,			\
												constellation	INT,			\
												sex				INT,			\
												blood			INT,			\
												marriage		INT,			\
												school			VARCHAR(50),	\
												degree			INT,			\
												metier			INT,			\
												position		INT,			\
												picturePath		VARCHAR(50),	\
												PRIMARY KEY		(id_SinglePageData) \
											); ");//userPicture		BLOB(5M),		
CString SQL_CreateTab2					= _T("IF object_id('T_Tab2') IS NULL CREATE TABLE T_Tab2(id_SinglePageData		INT NOT NULL, \
											 mobilePhone		VARCHAR(20),	\
											 companyPhone		VARCHAR(30),	\
											 homePhone			VARCHAR(30),	\
											 QQ1				VARCHAR(15),	\
											 QQ2				VARCHAR(15),	\
											 MSN1				VARCHAR(50),	\
											 MSN2				VARCHAR(50),	\
											 Skype1				VARCHAR(30),	\
											 Skype2				VARCHAR(30),	\
											 Email1				VARCHAR(50),	\
											 Email2				VARCHAR(50),	\
											 companyAddress		VARCHAR(100),	\
											 familyAddress		VARCHAR(100),	\
											 post				VARCHAR(100),	\
											 homePage			VARCHAR(30),	\
											 PRIMARY KEY		(id_SinglePageData) \
											 ); ");
CString SQL_CreateTab3					= _T("IF object_id('T_Tab3') IS NULL CREATE TABLE T_Tab3(id_SinglePageData INT NOT NULL, \
												id_Tab3ListVarStruct	INT,			\
												event					VARCHAR(50),	\
												meetdate				datetime,		\
												remark					VARCHAR(20),	\
												backupnbackup			VARCHAR(30),	\
												PRIMARY KEY				(id_SinglePageData, id_Tab3ListVarStruct) \
											 ); ");
CString SQL_CreateTab4					= _T("IF object_id('T_Tab4') IS NULL CREATE TABLE T_Tab4(id_SinglePageData	INT NOT NULL,	\
												id_Tab4ListVarStruct	INT,			\
												classify				VARCHAR(20),	\
												message					VARCHAR(100),	\
												PRIMARY KEY				(id_SinglePageData, id_Tab4ListVarStruct) \
											 ); ");
CString	SQL_INSERT_TAB					= _T(" INSERT INTO %s VALUES (%s); ");
CString SQL_UPDATE_TAB					= _T(" UPDATE %s SET %s  WHERE %s ; ");

